
import { getDatabase } from './init'
import { Pool } from 'pg'

// 自动创建数据库（如果不存在）
async function ensureDatabaseExists() {
  const dbName = process.env.DB_NAME || 'aide'
  const user = process.env.DB_USER || 'postgres'
  const password = process.env.DB_PASSWORD || 'unidt@123'
  const host = process.env.DB_HOST || 'localhost'
  const port = parseInt(process.env.DB_PORT || '5432')

  // 先连目标库，能连上就直接返回
  const testPool = new Pool({ database: dbName, user, password, host, port })
  try {
    await testPool.query('SELECT 1')
    await testPool.end()
    return
  } catch (e: any) {
    if (e.code !== '3D000') throw e // 不是数据库不存在的错误则抛出
  } finally {
    await testPool.end().catch(() => {})
  }

  // 连不上则连 postgres 数据库，尝试建库
  const adminPool = new Pool({ database: 'postgres', user, password, host, port })
  try {
    await adminPool.query(`CREATE DATABASE "${dbName}"`)
    console.log(`✅ 数据库 ${dbName} 已创建`)
  } catch (e: any) {
    if (e.code === '42P04') {
      // already exists
      console.log(`ℹ️ 数据库 ${dbName} 已存在`)
    } else {
      console.error('❌ 自动建库失败:', e)
      throw e
    }
  } finally {
    await adminPool.end().catch(() => {})
  }
}

// 创建用户行为追踪表
export async function createUserBehaviorTables(): Promise<void> {
  // 自动建库
  await ensureDatabaseExists()
  const client = await getDatabase().connect()
  
  try {
    console.log('🔄 删除旧表并重新创建用户行为追踪表...')
    
    // 删除旧表
    await client.query(`DROP TABLE IF EXISTS suggestion_feedback CASCADE`)
    await client.query(`DROP TABLE IF EXISTS user_behaviors CASCADE`)
    console.log('✅ 删除旧表完成')
    
    // 创建用户行为记录表
    await client.query(`
      CREATE TABLE user_behaviors (
        id TEXT PRIMARY KEY,
        user_id TEXT NOT NULL,
        action TEXT NOT NULL,
        entity_type TEXT NOT NULL CHECK (entity_type IN ('task', 'suggestion', 'search', 'filter')),
        entity_id TEXT,
        metadata JSONB,
        timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        
        -- 索引字段
        CONSTRAINT valid_action CHECK (action IN (
          'task_create', 'task_update', 'task_complete', 'task_delete',
          'suggestion_view', 'suggestion_accept', 'suggestion_dismiss', 'suggestion_rate',
          'search_query', 'search_click',
          'filter_apply', 'sort_apply',
          'page_visit', 'feature_use'
        ))
      )
    `)
    
    // 创建索引以提高查询性能
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_user_behaviors_user_id 
      ON user_behaviors(user_id)
    `)
    
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_user_behaviors_timestamp 
      ON user_behaviors(timestamp DESC)
    `)
    
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_user_behaviors_action 
      ON user_behaviors(action)
    `)
    
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_user_behaviors_entity 
      ON user_behaviors(entity_type, entity_id)
    `)
    
    // 创建AI建议反馈表
    await client.query(`
      CREATE TABLE suggestion_feedback (
        id TEXT PRIMARY KEY,
        user_id TEXT NOT NULL,
        suggestion_id TEXT NOT NULL,
        suggestion_type TEXT NOT NULL,
        action TEXT NOT NULL CHECK (action IN ('accept', 'dismiss', 'rate')),
        rating INTEGER CHECK (rating >= 1 AND rating <= 5),
        feedback_text TEXT,
        created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
      )
    `)
    
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_suggestion_feedback_user_id 
      ON suggestion_feedback(user_id)
    `)
    
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_suggestion_feedback_type 
      ON suggestion_feedback(suggestion_type)
    `)
    
    // 插入一些示例行为数据
    const sampleBehaviors = [
      {
        id: 'behavior_1',
        user_id: 'default_user',
        action: 'task_create',
        entity_type: 'task',
        entity_id: '1',
        metadata: JSON.stringify({ category: 'work', priority: 'P1' }),
        timestamp: new Date(Date.now() - 24 * 60 * 60 * 1000).toISOString() // 昨天
      },
      {
        id: 'behavior_2',
        user_id: 'default_user',
        action: 'task_complete',
        entity_type: 'task',
        entity_id: '1',
        metadata: JSON.stringify({ completionTime: 2.5 }),
        timestamp: new Date(Date.now() - 12 * 60 * 60 * 1000).toISOString() // 12小时前
      },
      {
        id: 'behavior_3',
        user_id: 'default_user',
        action: 'suggestion_accept',
        entity_type: 'suggestion',
        entity_id: 'sugg_1',
        metadata: JSON.stringify({ suggestionType: 'auto_schedule', rating: 4 }),
        timestamp: new Date(Date.now() - 6 * 60 * 60 * 1000).toISOString() // 6小时前
      }
    ]
    
    // 检查是否已有数据
    const existingBehaviors = await client.query('SELECT COUNT(*) FROM user_behaviors')
    if (parseInt(existingBehaviors.rows[0].count) === 0) {
      for (const behavior of sampleBehaviors) {
        await client.query(`
          INSERT INTO user_behaviors (id, user_id, action, entity_type, entity_id, metadata, timestamp)
          VALUES ($1, $2, $3, $4, $5, $6, $7)
        `, [behavior.id, behavior.user_id, behavior.action, behavior.entity_type, 
            behavior.entity_id, behavior.metadata, behavior.timestamp])
      }
      
      console.log('✅ 插入示例行为数据')
    }
    
    console.log('✅ 用户行为追踪表创建完成')
    
  } catch (error) {
    console.error('❌ 创建用户行为追踪表失败:', error)
    throw error
  } finally {
    client.release()
  }
}

// 运行迁移
if (require.main === module) {
  console.log('🚀 开始用户行为追踪数据库迁移...')
  createUserBehaviorTables()
    .then(() => {
      console.log('🎉 行为追踪数据库迁移完成!')
      process.exit(0)
    })
    .catch((error) => {
      console.error('❌ 迁移失败:', error)
      console.error('错误详情:', error.message)
      process.exit(1)
    })
}
